package com.zerosky.crm.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellStyle;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONArray;
import com.zerosky.crm.po.Branch;
import com.zerosky.crm.po.ChartSales;
import com.zerosky.crm.po.CustomerGrowth;
import com.zerosky.crm.po.Department;
import com.zerosky.crm.po.Employee;
import com.zerosky.crm.po.ExportAllChartSales;
import com.zerosky.crm.service.BranchService;
import com.zerosky.crm.service.ChartService;
import com.zerosky.crm.service.DepartmentService;
import com.zerosky.crm.service.EmployeeService;
import com.zerosky.crm.util.BaseAction;
import com.zerosky.crm.util.ExcelUtil;
import com.zerosky.crm.util.MyFileUploadUtils;

/**
 * 利用POI技术导出excel文件
 * 
 * @author yanglingchao
 *
 */
@Controller
public class ExportExcelByPoiController extends BaseAction {
	private Logger logger = LoggerFactory.getLogger(CustomerController.class); // 日志

	@Resource(name = "chartService")
	ChartService chartService;

	@Resource(name = "departmentService")
	DepartmentService departmentService;

	@Resource(name = "branchService")
	BranchService branchService;

	@Resource(name = "employeeService")
	EmployeeService employeeService;

	/**
	 * 导出销售排名图表功能的统计数据
	 * 
	 * @param response
	 * @param request
	 * @param session
	 */
	@RequestMapping(value = "rankCharExport")
	@ResponseBody
	public void rankCharExport(HttpServletResponse response, HttpServletRequest request, HttpSession session) {
		JSONArray ja = new JSONArray();
		String method = "emp";
		String type = request.getParameter("type");
		String date = request.getParameter("date");
		String deptOption = request.getParameter("deptOption");
		String branchOption = request.getParameter("branchOption");
		String teamOption = request.getParameter("teamOption");
		Boolean canNotReadAllMessage = Boolean.parseBoolean(request.getParameter("canNotReadAllMessage"));

		if ("this".equals(type)) {

			Map<String, Object> json = new HashMap<>();
			Map<String, Object> returnMap = new HashMap<>();
			Employee user = (Employee) session.getAttribute("loginUser");// 当前登录对象
			int isBranchTeamEmp = 0;
			try {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("method", method);
				map.put("date", date);
				map.put("deptId", deptOption);
				map.put("branchId", branchOption);
				map.put("teamId", teamOption);

				// 选择团队下拉框，查询员工排名
				if (teamOption != "" && teamOption != null) {
					map.put("teamOption", teamOption);
					map.put("method", method);
					isBranchTeamEmp = 1;
				}
				// 选择部门下拉框，查询团队排名
				else if (branchOption != "" && branchOption != null) {
					if (canNotReadAllMessage && user.getTeam() != null) {
					} else {
						map.put("branchOption", branchOption);
						map.put("method", "team");
						isBranchTeamEmp = 2;
					}
				}
				// 选择分公司下拉框，查询部门排名
				else if (deptOption != "" && deptOption != null) {
					if (canNotReadAllMessage && user.getBranch() != null) {
					} else {
						map.put("deptOption", deptOption);
						map.put("method", "branch");
						isBranchTeamEmp = 3;
					}

				}
				// 登录，没有选择任何下拉框
				else {
					if (!canNotReadAllMessage) {
						map.put("canNotReadAllMessage", canNotReadAllMessage);
						map.put("method", "dept");
						isBranchTeamEmp = 0;
					}
					// 如果登录用户有团队id，说明是团队长，默认查询团队中员工的排名
					else if (user.getTeam() != null) {
						map.put("teamOption", user.getTeam().getBranchId());
						map.put("method", "emp");
						map.put("teamId", user.getTeam().getBranchId());
						isBranchTeamEmp = 1;
					}
					// 如果登录用户没有有团队id，但拥有部门id，说明是部门经理，默认查询部门中团队的排名
					else if (user.getBranch() != null) {
						map.put("branchOption", user.getBranch().getBranchId());
						map.put("method", "team");
						map.put("branchId", user.getBranch().getBranchId());
						isBranchTeamEmp = 2;
					}
					// 如果登录用户没有有团队id和部门id，但拥有分公司id，说明是分公司经理，默认查询所在分公司中部门的排名
					else if (user.getDept() != null) {
						map.put("deptOption", user.getDept().getDeptId());
						map.put("method", "branch");
						map.put("deptId", user.getDept().getDeptId());
						isBranchTeamEmp = 3;
					}
				}
				// 用于判断是查询哪个级别的排名
				map.put("isBranchTeamEmp", isBranchTeamEmp);
				// 执行 查询
				List<ChartSales> list = chartService.getRankChartList(map);
				String emp[] = new String[list.size()];
				String dept[] = new String[list.size()];
				String countSale[] = new String[list.size()];
				Integer index = 0;
				List<Map<String, Object>> data = new ArrayList<>();
				List<Map<String, Object>> reuturnlist = new ArrayList<>();
				for (ChartSales chart : list) {
					Map<String, Object> dataMap = new LinkedHashMap<>();
					// 判断统计了什么的销售量排名
					if (isBranchTeamEmp == 0) {
						emp[index] = chart.getDept().getDeptName();
						returnMap = new HashMap<>();
						returnMap.put("name", chart.getDept().getDeptName());
						returnMap.put("countSalesAmount", chart.getCountSalesAmount());
					} else if (isBranchTeamEmp == 1) {
						emp[index] = chart.getEmpName();
						returnMap = new HashMap<>();
						returnMap.put("name", chart.getEmpName());
						returnMap.put("countSalesAmount", chart.getCountSalesAmount());
					} else if (isBranchTeamEmp == 2) {
						emp[index] = chart.getTeam().getBranchName();
						returnMap = new HashMap<>();
						returnMap.put("name", chart.getTeam().getBranchName());
						returnMap.put("countSalesAmount", chart.getCountSalesAmount());
					} else if (isBranchTeamEmp == 3) {
						emp[index] = chart.getBranch().getBranchName();
						returnMap = new HashMap<>();
						returnMap.put("name", chart.getBranch().getBranchName());
						returnMap.put("countSalesAmount", chart.getCountSalesAmount());
					}
					countSale[index] = chart.getCountSalesAmount().toString();
					dataMap.put("value", chart.getCountSalesAmount());
					reuturnlist.add(returnMap);
					ja.add(returnMap);
					data.add(dataMap);
					index++;
				}
				json.put("emp", emp);
				json.put("dept", dept);
				json.put("data", data);
				json.put("list", reuturnlist);
				json.put("countSale", countSale);
				json.put("code", 1);
			} catch (Exception e) {
				json.put("code", -1);
				logger.error("获取数据失败，原因：" + e);
			}
			Map<String, String> headMap = new LinkedHashMap<String, String>();

			headMap.put("name", "名称");
			headMap.put("countSalesAmount", "销售额");
			String title = null;
			try {
				// title = new
				// String(request.getParameter("title").getBytes("iso-8859-1"),"utf-8");
				title = request.getParameter("title");
			} catch (Exception e) {
				logger.error("获取数据失败，原因：" + e);
			}
			ExcelUtil.downloadExcelFile(title, headMap, ja, response);
		} else {
			Map<String, Object> map = new HashMap<String, Object>();
			boolean checkPower = checkPower(session, 44);
			map.put("date", date);
			map.put("deptId", deptOption);
			map.put("branchId", branchOption);
			map.put("teamId", teamOption);
			map.put("checkPower", checkPower);
			List<ExportAllChartSales> exportAllChartSales = chartService.getExportAllChartSales(map);
			for (ExportAllChartSales exportAllChartSale : exportAllChartSales) {
				ja.add(exportAllChartSale);
			}

			Map<String, String> headMap = new LinkedHashMap<String, String>();
			headMap.put("deptName", "分公司");
			headMap.put("branchName", "部门");
			headMap.put("teamName", "团队");
			headMap.put("empName", "姓名");
			headMap.put("countSalesAmount", "销售额");
			String title = null;
			try {
				// title = new
				// String(request.getParameter("title").getBytes("iso-8859-1"),"utf-8");
				title = request.getParameter("title");
			} catch (Exception e) {
				logger.error("获取数据失败，原因：" + e);
			}
			ExcelUtil.downloadExcelFile(title, headMap, ja, response);
		}

	}

	/**
	 * 导出销售额统计图表功能的统计数据
	 * 
	 * @param response
	 * @param request
	 * @param session
	 */
	@RequestMapping(value = "teamCharExport")
	@ResponseBody
	public void teamCharExport(HttpServletResponse response, HttpServletRequest request, HttpSession session) {
		JSONArray ja = new JSONArray();
		String method = request.getParameter("type");
		String date = request.getParameter("date");
		String deptId = request.getParameter("deptId");
		String branchId = request.getParameter("branchId");
		String teamId = request.getParameter("teamId");
		Boolean canNotReadAllMessage = Boolean.parseBoolean(request.getParameter("canNotReadAllMessage"));
		String status = request.getParameter("status");

		if ("this".equals(status)) {

			Map<String, Object> json = new HashMap<>();
			Employee user = (Employee) session.getAttribute("loginUser");// 当前登录对象
			try {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("method", method);
				map.put("date", date);
				map.put("deptId", deptId);
				map.put("branchId", branchId);
				map.put("teamId", teamId);

				// 设置默认登录查询的信息
				if (method == null || method == "") {
					// 拥有查询所有信息的权限
					if (!canNotReadAllMessage) {
						map.put("method", "");
					}
					// 如果登录用户有团队id，说明是团队长，默认查询团队中员工的排名
					else if (user.getTeam() != null) {
						map.put("method", "team");
						map.put("teamId", user.getTeam().getBranchId());
						method = "team";
					}
					// 如果登录用户没有有团队id，但拥有部门id，说明是部门经理，默认查询部门中团队的排名
					else if (user.getBranch() != null) {
						map.put("method", "branch");
						map.put("branchId", user.getBranch().getBranchId());
						method = "branch";
					}
					// 如果登录用户没有有团队id和部门id，但拥有分公司id，说明是分公司经理，默认查询所在分公司中部门的排名
					else if (user.getDept() != null) {
						map.put("method", "dept");
						map.put("deptId", user.getDept().getDeptId());
						method = "dept";
					}
				}
				List<ChartSales> list = chartService.getChartSaleList(map);
				String dept[] = new String[list.size()];
				String countSale[] = new String[list.size()];
				Integer index = 0;
				List<Map<String, Object>> data = new ArrayList<>();
				for (ChartSales chart : list) {
					Map<String, Object> dataMap = new LinkedHashMap<>();
					if (method == null || method == "") {
						dept[index] = chart.getDept().getDeptName();
						dataMap.put("name", chart.getDept().getDeptName());
					} else if ("branch".equals(method)) {
						dept[index] = chart.getTeam().getBranchName();
						dataMap.put("name", chart.getTeam().getBranchName());
					} else if ("dept".equals(method)) {
						dept[index] = chart.getBranch().getBranchName();
						dataMap.put("name", chart.getBranch().getBranchName());
					} else if ("team".equals(method)) {
						dept[index] = chart.getEmpName();
						dataMap.put("name", chart.getEmpName());
					}
					countSale[index] = chart.getCountSalesAmount().toString();
					dataMap.put("value", chart.getCountSalesAmount());
					data.add(dataMap);
					ja.add(dataMap);
					index++;
				}
				json.put("dept", dept);
				json.put("data", data);
				json.put("countSale", countSale);
				json.put("code", 1);
			} catch (Exception e) {
				json.put("code", -1);
				logger.error("获取数据失败，原因：" + e);
			}

			Map<String, String> headMap = new LinkedHashMap<String, String>();

			headMap.put("name", "名称");
			headMap.put("value", "销售额");
			String title = null;
			try {
				// title = new
				// String(request.getParameter("title").getBytes("iso-8859-1"),"utf-8");
				title = request.getParameter("title");
			} catch (Exception e) {
				logger.error("获取数据失败，原因：" + e);
			}
			ExcelUtil.downloadExcelFile(title, headMap, ja, response);
		} else {
			Map<String, Object> map = new HashMap<String, Object>();
			boolean checkPower = checkPower(session, 45);
			map.put("date", date);
			map.put("deptId", deptId);
			map.put("branchId", branchId);
			map.put("teamId", teamId);
			map.put("checkPower", checkPower);
			List<ExportAllChartSales> exportAllChartSales = chartService.getExportAllChartSales(map);
			for (ExportAllChartSales exportAllChartSale : exportAllChartSales) {
				ja.add(exportAllChartSale);
			}

			Map<String, String> headMap = new LinkedHashMap<String, String>();
			headMap.put("deptName", "分公司");
			headMap.put("branchName", "部门");
			headMap.put("teamName", "团队");
			headMap.put("empName", "姓名");
			headMap.put("countSalesAmount", "销售额");
			String title = null;
			try {
				title = new String(request.getParameter("title").getBytes("iso-8859-1"), "utf-8");
			} catch (UnsupportedEncodingException e) {
				logger.error("获取数据失败，原因：" + e);
			}
			ExcelUtil.downloadExcelFile(title, headMap, ja, response);
		}
	}

	/**
	 * 导出图表客户增长
	 * 
	 * @param response
	 * @param request
	 * @param session
	 */
	@RequestMapping(value = "customerGrowthExport")
	@ResponseBody
	public void customerGrowthExport(HttpServletResponse response, HttpServletRequest request, HttpSession session) {
		JSONArray ja = new JSONArray();
		String type = request.getParameter("type");
		String dateType = request.getParameter("dateType");
		String deptId = request.getParameter("deptId");
		String branchId = request.getParameter("branchId");
		String teamId = request.getParameter("teamId");
		String status = request.getParameter("status");

		if ("this".equals(status)) {

			Map<String, Object> json = new HashMap<String, Object>();
			Map<String, Object> map = new HashMap<String, Object>();
			Employee user = (Employee) request.getSession().getAttribute("loginUser");
			// 设置权限，如果menuId中有46查看所有销售排名情况的，则有所有查看权限
			Boolean canNotReadAllMessage = checkPower(session, 46);
			try {
				// 查询参数
				map.put("type", type);
				map.put("deptId", deptId);
				map.put("branchId", branchId);
				map.put("teamId", teamId);
				map.put("dateType", dateType);
				if (dateType == null || "".equals(dateType)) {
					map.put("dateType", "nowYear");
				}
				if (!canNotReadAllMessage) {
					map.put("isDeptUser", "1");
					map.put("deptId", user.getDept().getDeptId());
				} else {
					map.put("isDeptUser", null);
				}
				List<CustomerGrowth> customerGrowthList = chartService.getCustomerGrowthChart(map);
				String max[] = new String[customerGrowthList.size()];
				String dept[] = new String[customerGrowthList.size()];
				String personNumbers[] = new String[customerGrowthList.size()];
				Integer index = 0;
				for (CustomerGrowth increatNum : customerGrowthList) {
					dept[index] = increatNum.getDeptName();
					personNumbers[index] = increatNum.getPersonNumber().toString();
					index++;
				}
				json.put("data", customerGrowthList);
				json.put("dept", dept);
				json.put("max", max);
				json.put("personNumbers", personNumbers);

				for (int i = 0; i < dept.length; i++) {
					Map<String, String> resultMap = new HashMap<>();
					resultMap.put("name", dept[i]);
					resultMap.put("value", personNumbers[i]);
					ja.add(resultMap);
				}
			} catch (Exception e) {
				logger.error("获取数据失败，原因：" + e);
			}

			Map<String, String> headMap = new LinkedHashMap<String, String>();
			headMap.put("name", "名称");
			headMap.put("value", "客户数");
			String title = null;
			try {
				// title = new
				// String(request.getParameter("title").getBytes("iso-8859-1"),"utf-8");
				title = request.getParameter("title");
			} catch (Exception e) {
				logger.error("获取数据失败，原因：" + e);
			}
			ExcelUtil.downloadExcelFile(title, headMap, ja, response);

		} else {
			Map<String, Object> map = new HashMap<String, Object>();
			boolean checkPower = checkPower(session, 45);
			map.put("date", dateType);
			map.put("deptId", deptId);
			map.put("branchId", branchId);
			map.put("teamId", teamId);
			map.put("checkPower", checkPower);
			List<ExportAllChartSales> exportAllChartSales = chartService.getExportAllCustomer(map);
			for (ExportAllChartSales exportAllChartSale : exportAllChartSales) {
				ja.add(exportAllChartSale);
			}

			Map<String, String> headMap = new LinkedHashMap<String, String>();
			headMap.put("deptName", "分公司");
			headMap.put("branchName", "部门");
			headMap.put("teamName", "团队");
			headMap.put("empName", "姓名");
			headMap.put("countSalesAmount", "客户数");
			String title = null;
			try {
				// title = new
				// String(request.getParameter("title").getBytes("iso-8859-1"),"utf-8");
				title = request.getParameter("title");
			} catch (Exception e) {
				logger.error("获取数据失败，原因：" + e);
			}
			ExcelUtil.downloadExcelFile(title, headMap, ja, response);
		}
	}

	// 创建Excel
	@RequestMapping("/createExcel.do")
	public String createExcel(HttpServletResponse response, HttpServletRequest request, HttpSession session)
			throws IOException {
		Employee user = (Employee) session.getAttribute("loginUser");
		if (checkPower(session, 65) && (user.getQuarters().equals("5") || user.getQuarters().equals("6")
				|| user.getQuarters().equals("4"))) {
			// 创建HSSFWorkbook对象(excel的文档对象)
			HSSFWorkbook wb = new HSSFWorkbook();
			// 建立新的sheet对象（excel的表单）
			HSSFSheet sheet = wb.createSheet("导出员工数据列表");
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
			cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
			// 设置列宽
			sheet.autoSizeColumn(1, true);
			sheet.setColumnWidth(0, 3766);
			sheet.setColumnWidth(1, 3766);
			sheet.setColumnWidth(2, 3766);
			sheet.setColumnWidth(3, 3766);
			sheet.setColumnWidth(4, 3766);
			sheet.setColumnWidth(5, 3766);
			sheet.setColumnWidth(6, 3766);
			sheet.setColumnWidth(7, 3766);
			sheet.setColumnWidth(8, 3766);
			// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
			HSSFRow row1 = sheet.createRow(0);
			// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
			HSSFCell cell = row1.createCell(0);
			// 设置单元格内容
			cell.setCellValue("员工信息一览表");

			// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
			// 在sheet里创建第二行
			HSSFRow row2 = sheet.createRow(1);
			// 创建单元格并设置单元格内容
			row2.createCell(0).setCellValue("姓名（必填）");
			row2.createCell(1).setCellValue("账号");
			row2.createCell(2).setCellValue("手机号码（必填）");
			row2.createCell(3).setCellValue("性别");
			row2.createCell(4).setCellValue("职务");
			row2.createCell(5).setCellValue("分公司（必填）");
			row2.createCell(6).setCellValue("分类（必填）");
			row2.createCell(7).setCellValue("部门");
			row2.createCell(8).setCellValue("团队");
			List<Employee> empList = employeeService.getEmployeeListAll(null);
			for (int i = 0; i < empList.size(); i++) {
				// 在sheet里创建行对象并赋予内容
				HSSFRow row3 = sheet.createRow(i + 2);
				row3.createCell(0).setCellValue(empList.get(i).getName());
				row3.createCell(1).setCellValue(empList.get(i).getUserCard());
				row3.createCell(2).setCellValue(empList.get(i).getPhoneNumber());
				row3.createCell(3).setCellValue(empList.get(i).getSex());
				row3.createCell(4).setCellValue(empList.get(i).getPosition());
				row3.createCell(5).setCellValue(empList.get(i).getDept().getDeptName());
				row3.createCell(6).setCellValue(empList.get(i).getDept().getRemake());
				if (empList.get(i).getBranch() != null) {
					row3.createCell(7).setCellValue(empList.get(i).getBranch().getBranchName());
				}
				if (empList.get(i).getTeam() != null) {
					row3.createCell(8).setCellValue(empList.get(i).getTeam().getBranchName());
				}
			}
			// 输出Excel文件
			OutputStream output = response.getOutputStream();
			response.reset();
			response.setHeader("Content-disposition", "attachment; filename=employeeList.xls");
			response.setContentType("application/msexcel");
			wb.write(output);
			output.close();
		}
		return null;
	}

	@RequestMapping({ "/import_employeeExcel.do" })
	@ResponseBody
	public Map<String, Object> addExcel(@RequestParam("uploadFile") MultipartFile file, HttpServletResponse response,
			HttpServletRequest request, HttpSession session) {
		Employee user = (Employee) session.getAttribute("loginUser");
		Map<String, Object> json = new HashMap<String, Object>();
		if (checkPower(session, 65) && (user.getQuarters().equals("5") || user.getQuarters().equals("6")
				|| user.getQuarters().equals("4"))) {
			Map<String, Object> deptMap = new HashMap<String, Object>();
			Map<String, Object> branchMap = new HashMap<String, Object>();
			Map<String, Object> teamMap = new HashMap<String, Object>();
			deptMap.put("canNotReadAllMessage", false);
			branchMap.put("isBranch", "isBranch");
			List<Department> deptList = departmentService.getDepartmentList(deptMap);
			List<Branch> branchList = branchService.getBranchAllList(branchMap);
			List<Branch> teamList = branchService.getBranchAllList(teamMap);
			String basePath = "upload/";
			// 定义一个文件路径来获取文件的相对路径
			String fileUrl = MyFileUploadUtils.saveFileName(request, file, basePath);
			try {
				basePath += fileUrl;
				String basePathUrl = request.getSession().getServletContext().getRealPath(basePath);
				// 获取excel数据
				List<Employee> list = ExcelUtil.readExcel(basePathUrl, deptList, branchList, teamList, user.getName());
				List<String> listName = new ArrayList<String>();
				for (Employee emp : list) {
					Map<String, Object> map = new HashMap<String, Object>();
					map.put("userCard", emp.getUserCard());// 账号
					map.put("phoneNumber", emp.getPhoneNumber());// 手机号码
					if (employeeService.checkIsExist(map) > 0) {// 验证是否存在相同账号或者手机号码
						listName.add(emp.getName());
					}
				}
				if (listName.size() == 0) {
					employeeService.addEmployeeList(list);
				} else {
					json.put("listName", listName);
					json.put("code", -4);
					return json;
				}
				json.put("code", 0);
			} catch (DuplicateKeyException e) {
				e.printStackTrace();
				json.put("code", -1);
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
				json.put("code", -2);
			} catch (Exception e) {
				e.printStackTrace();
				json.put("code", -3);
			}
		} else {
			json.put("code", -5);
		}
		return json;
	}

}
